package com.pangu.export.utils;

import com.pangu.export.annotation.ExcelCell;
import com.pangu.export.constant.ExcelType;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.RegionUtil;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.lang.reflect.Field;
import java.lang.reflect.Type;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.regex.Pattern;

/**
 * Created by etfox on 2019/05/20 13:14
 */
public class ExcelWriteUtil {

    /**
     * 根据模板 获取/创建 一个Workbook
     *
     * @param startSheet 指定Sheet
     * @param startRow   开始行
     * @param tempPath   模板路径
     * @param fileType   文件格式
     * @param list       数据
     * @param clazz      对象
     * @return workbook
     * @throws IOException e
     */
    public static <T> Workbook writeExcel(Integer startSheet, Integer startRow, String tempPath, String fileType, List<T> list, Class<T> clazz) throws IOException, NoSuchFieldException, IllegalAccessException {
        Workbook workbook = createWorkbook(tempPath, fileType);
        workbook = writeExcel(workbook, startSheet, startRow, list, clazz);

        return workbook;
    }

    /**
     * 指定模板构建写入数据对象
     *
     * @param startSheet 指定sheet
     * @param tempPath   模板路径
     * @param fileType   模板文件格式
     * @param object     数据对象
     * @return workbook
     * @throws IOException            e
     * @throws IllegalAccessException e
     */
    public static <T> Workbook writeExcel(Integer startSheet, String tempPath, String fileType, T object) throws IOException, IllegalAccessException {
        Workbook workbook = createWorkbook(tempPath, fileType);
        workbook = writeExcel(workbook, startSheet, object);

        return workbook;
    }

    /**
     * 指定模板或者指定列头
     *
     * @param sheetName sheet名称
     * @param startRow  开始行
     * @param tempPath  模板路径
     * @param fileType  文件格式
     * @param headers   列头
     * @param list      数据
     * @param clazz     对象
     * @return workbook
     */
    public static <T> Workbook writeExcel(String sheetName, Integer startRow, String tempPath, String fileType, String[] headers, List<T> list, Class<T> clazz) throws IOException, NoSuchFieldException, IllegalAccessException {
        Workbook workbook = createWorkbook(tempPath, fileType);

        Sheet sheet = workbook.createSheet(sheetName);// 在workbook中添加一个sheet, 对应Excel文件中的sheet
        Integer startSheet = workbook.getSheetIndex(sheet);// 得到表索引
        Row createRow = sheet.createRow(startRow);// 创建对应行
        for (int i = 0; i < headers.length; i++) {
            createRow.createCell(i).setCellValue(headers[i]);// 创建第几个cell
            sheet.setColumnWidth(i, headers[i].getBytes().length * 2 * 256);// 设置列宽
        }

        workbook = writeExcel(workbook, startSheet, startRow, list, clazz);
        return workbook;
    }

    /**
     * 指定模板,根据 列头 和 体 循环创建
     * 必须指定模板
     *
     * @param sheetName sheet名称
     * @param headers   列头
     * @param data      数据
     * @param clazz     对象
     * @return workbook
     */
    public static <T> Workbook writeExcel(String sheetName, String tempPath, String fileType, String title, String[] headers, Map<String, List<T>> data, Class<T> clazz)
            throws Exception {
        // 模板
        Workbook temp_workbook = createWorkbook(tempPath, fileType);
        Sheet temp_sheet = temp_workbook.getSheetAt(0);
        // 模板
        Row temp_row1 = temp_sheet.getRow(0);
        Row temp_row2 = temp_sheet.getRow(1);
        Row temp_row3 = temp_sheet.getRow(2);
        Row temp_row4 = temp_sheet.getRow(3);
        Row temp_row5 = temp_sheet.getRow(4);
        Row temp_row6 = temp_sheet.getRow(5);

        // 第一步，创建一个Workbook，对应一个Excel文件
        Workbook workbook = createWorkbook("", fileType);

        // 单元格金额格式
        CellStyle moneyCellStyle = getMoneyCellStyle(workbook);

        // 单元格数字格式
        CellStyle numberCellStyle = getNumberCellStyle(workbook);

        // 单元格字符串格式
        CellStyle characterCellStyle = getCharacterCellStyle(workbook);

        // 单元格序号格式
        CellStyle noCellStyle = getNoCellStyle(workbook);

        // 单元格换行格式
        CellStyle warpTextStyle = getWrapTextStyle(workbook);

        // 在workbook中添加一个sheet, 对应Excel文件中的sheet
        Sheet sheet = workbook.createSheet(sheetName);

        // 创建大标题
        Row createRow = sheet.createRow(0);// 创建第一行
        // 标题合并单元格
        // POI导出EXCEL设置跨行跨列（在所有数据行和列创建完成后再执行）
        addMergedRegion(workbook, sheet, temp_row1.getCell(0).getCellStyle(), 0, 0, 0, headers.length - 1);

        Cell createCell = createRow.createCell(0);// 声明列对象
        createCell.setCellValue(title);// 设置顶层标题
        CellStyle toCellStyle = workbook.createCellStyle();
        copyCellStyle(temp_row1.getCell(0).getCellStyle(), toCellStyle);
        setDefaultBorder(toCellStyle);
        createCell.setCellStyle(toCellStyle);

        // 创建空白行
        createRow = sheet.createRow(sheet.getLastRowNum() + 1);// 向下偏移
        addMergedRegion(workbook, sheet, temp_row1.getCell(0).getCellStyle(), sheet.getLastRowNum(), sheet.getLastRowNum(), 0, headers.length - 1);

        createCell = createRow.createCell(0);
        toCellStyle = workbook.createCellStyle();
        copyCellStyle(temp_row1.getCell(0).getCellStyle(), toCellStyle);
        setDefaultBorder(toCellStyle);
        createCell.setCellStyle(toCellStyle);

        // 字段对应列
        Map<String, Integer> cellMap = new HashMap<>();
        for (Field _f : clazz.getDeclaredFields()) {
            ExcelCell _cell = _f.getAnnotation(ExcelCell.class);
            if (_cell != null) cellMap.put(_f.getName(), _cell.cellX());
        }

        // 数据处理
        Set<String> keySet = data.keySet();
        for (String key : keySet) {
            // 创建表名
            createRow = sheet.createRow(sheet.getLastRowNum() + 1);// 向下偏移
            // POI导出EXCEL设置跨行跨列（在所有数据行和列创建完成后再执行）
            addMergedRegion(workbook, sheet, temp_row2.getCell(0).getCellStyle(), sheet.getLastRowNum(), sheet.getLastRowNum(), 0, headers.length - 1);
            Cell cell_table = createRow.createCell(0);
            toCellStyle = workbook.createCellStyle();
            copyCellStyle(temp_row2.getCell(0).getCellStyle(), toCellStyle);
            cell_table.setCellStyle(toCellStyle);
            cell_table.setCellValue("表名: " + key);

            // 创建描述
            createRow = sheet.createRow(sheet.getLastRowNum() + 1);// 向下偏移
            // POI导出EXCEL设置跨行跨列（在所有数据行和列创建完成后再执行）
            addMergedRegion(workbook, sheet, temp_row3.getCell(0).getCellStyle(), sheet.getLastRowNum(), sheet.getLastRowNum(), 0, headers.length - 1);

            Cell cell_describe = createRow.createCell(0);
            toCellStyle = workbook.createCellStyle();
            copyCellStyle(temp_row3.getCell(0).getCellStyle(), toCellStyle);
            setDefaultBorder(toCellStyle);
            cell_describe.setCellStyle(toCellStyle);

            // 创建标题
            createRow = sheet.createRow(sheet.getLastRowNum() + 1);// 向下偏移1行
            Cell cell_table_row;
            for (int i = 0; i < headers.length; i++) {
                cell_table_row = createRow.createCell(i);// 单元格从第二列开始
                cell_table_row.setCellValue(headers[i]);
                toCellStyle = workbook.createCellStyle();
                copyCellStyle(temp_row4.getCell(i).getCellStyle(), toCellStyle);
                setDefaultBorder(toCellStyle);

                cell_table_row.setCellStyle(toCellStyle);
            }

            List<T> data_line = data.get(key);
            // 创建内容
            for (T t : data_line) {
                createRow = sheet.createRow(sheet.getLastRowNum() + 1);// 向下偏移1行
                for (Map.Entry<String, Integer> cellEntry : cellMap.entrySet()) {
                    String fieldName = cellEntry.getKey();
                    int cellPos = cellEntry.getValue();
                    // 获取对象中字段的值
                    Field field = t.getClass().getDeclaredField(fieldName);
                    if (null == field) continue;
                    field.setAccessible(true);
                    if (cellPos == -2) {
                        Object obj = field.get(t);
                        cell_describe.setCellValue("描述: " + obj.toString());
                        continue;
                    }
                    if (cellPos < 0) continue;
                    Cell curCell = createRow.createCell(cellPos);
                    toCellStyle = workbook.createCellStyle();
                    copyCellStyle(temp_row5.getCell(cellPos).getCellStyle(), toCellStyle);
                    curCell.setCellStyle(toCellStyle);// 设置样式

                    setCellValue(workbook, curCell, moneyCellStyle, numberCellStyle, characterCellStyle, warpTextStyle, t, field);
                }
            }
            // 创建隔离行
            createRow = sheet.createRow(sheet.getLastRowNum() + 1);// 向下偏移1行
            // POI导出EXCEL设置跨行跨列（在所有数据行和列创建完成后再执行）
            addMergedRegion(workbook, sheet, temp_row6.getCell(0).getCellStyle(), sheet.getLastRowNum(), sheet.getLastRowNum(), 0, headers.length - 1);
            Cell cell_next = createRow.createCell(0);
            toCellStyle = workbook.createCellStyle();
            copyCellStyle(temp_row6.getCell(0).getCellStyle(), toCellStyle);
            cell_next.setCellStyle(toCellStyle);
        }

//        sheet = workbook.createSheet("颜色参考页");
//        int rowNum = 0;
//        for(int i = 8; i <= 64; i++){
//            if (i==27 || (i<40 && i>31)) continue;
//
//
//            Row row = sheet.createRow((short) rowNum);
//            CellStyle style = workbook.createCellStyle();
//            style.setFillForegroundColor((short) i);
//            style.setFillPattern(CellStyle.SOLID_FOREGROUND);
//
//            Cell cell = row.createCell((short) 1);
//            cell.setCellStyle(style);
//            cell = row.createCell((short) 2);
//            cell.setCellValue((short) i);
//
//            rowNum++;
//        }


        return workbook;
    }

    /**
     * @param workbook   workbook
     * @param startSheet 起始页
     * @param startRow   起始行
     * @param list       数据
     * @param clazz      对象
     * @return workbook
     * @throws NoSuchFieldException e
     */
    public static <T> Workbook writeExcel(Workbook workbook, Integer startSheet, Integer startRow, List<T> list, Class<T> clazz) throws NoSuchFieldException, IllegalAccessException {
        if (null == list || list.size() == 0) {
            return workbook;
        }

        // 单元格金额格式
        CellStyle moneyCellStyle = getMoneyCellStyle(workbook);

        // 单元格数字格式
        CellStyle numberCellStyle = getNumberCellStyle(workbook);

        // 单元格字符串格式
        CellStyle characterCellStyle = getCharacterCellStyle(workbook);

        // 单元格序号格式
        CellStyle noCellStyle = getNoCellStyle(workbook);

        // 单元格换行格式
        CellStyle warpTextStyle = getWrapTextStyle(workbook);

        // 设置居中
        // warpStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);

        // 起始工作簿
        if (null == startSheet) startSheet = 0;
        if (null == startRow) startRow = 1;

        // 字段对应列
        Map<String, Integer> cellMap = new HashMap<>();
        for (Field _f : clazz.getDeclaredFields()) {
            ExcelCell _cell = _f.getAnnotation(ExcelCell.class);
            if (_cell != null) {
                cellMap.put(_f.getName(), _cell.cellY());
            }
        }

        // HSSFSheet、XSSFSheet
        Sheet sheet = workbook.getSheetAt(startSheet);

        // 填充数据
        int createIndex = startRow;

        for (T model : list) {
            // 新建行
            Row createRow = sheet.getRow(createIndex);
            // 为了保留原有样式
            if (null == createRow) createRow = sheet.createRow(createIndex);
            // 行高
            createRow.setHeight((short) (20 * 15));
            Class<?> obj = model.getClass();

            for (Map.Entry<String, Integer> cellEntry : cellMap.entrySet()) {
                String fieldName = cellEntry.getKey();
                int cellPos = cellEntry.getValue();
                // 获取对象中字段的值
                Field field = obj.getDeclaredField(fieldName);
                if (null == field) {
                    continue;
                }
                field.setAccessible(true);
                Cell curCell = createRow.createCell(cellPos);
                if (null == curCell) {
                    continue;
                }
                // CellStyle 改为参数形式, 为的是不必要重复创建 CellStyle, 浪费内存
                setCellValue(workbook, curCell, moneyCellStyle, numberCellStyle, characterCellStyle, warpTextStyle, model, field);
            }
            // 累加
            createIndex++;
        }

        return workbook;
    }

    /**
     * 根据Workbook写入数据对象
     *
     * @param workbook   workbook
     * @param startSheet 指定sheet
     * @param object     数据对象
     * @return workbook
     */
    public static <T> Workbook writeExcel(Workbook workbook, Integer startSheet, T object) throws IllegalAccessException {
        // 单元格金额格式
        CellStyle moneyCellStyle = getMoneyCellStyle(workbook);

        // 单元格数字格式
        CellStyle numberCellStyle = getNumberCellStyle(workbook);

        // 单元格字符串格式
        CellStyle characterCellStyle = getCharacterCellStyle(workbook);

        // 单元格序号格式
        CellStyle noCellStyle = getNoCellStyle(workbook);

        // 单元格换行格式
        CellStyle warpTextStyle = getWrapTextStyle(workbook);

        // 设置居中
        // warpStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        if (null != object) {
            Class<?> clazz = object.getClass();
            for (Field field : clazz.getDeclaredFields()) {

                field.setAccessible(true);

                ExcelCell excelCell = field.getAnnotation(ExcelCell.class);
                if (null == excelCell) {
                    continue;
                }

                int sheetNum = excelCell.sheet();
                int cellXNum = excelCell.cellX();
                int cellYNum = excelCell.cellY();

                //起始工作簿(如果对象没有指定Sheet则使用默认Sheet)
                if (null != startSheet) sheetNum = startSheet;

                Sheet sheet = workbook.getSheetAt(sheetNum);

                Cell cell;
                //获取合并单元格
                cell = ExcelReadUtil.getMergedRegionCell(sheet, cellXNum, cellYNum);
                if (null == cell) {
                    Row curRow = sheet.getRow(cellXNum);
                    //为了保留原有样式
                    if (null == curRow) {
                        curRow = sheet.createRow(cellXNum);
                    }
                    cell = curRow.getCell(cellYNum);
                    if (null == cell) {
                        cell = curRow.createCell(cellYNum);
                    }
                }
                if (null == cell) {
                    continue;
                }
                setCellValue(workbook, cell, moneyCellStyle, numberCellStyle, characterCellStyle, warpTextStyle, object, field);
            }
        }

        return workbook;
    }

    /**
     * 根据模板创建
     *
     * @param tempPath 模板全路径
     * @param fileType 模板路径为null时, 新建 fileType 格式 Workbook
     * @return Workbook
     */
    public static Workbook createWorkbook(String tempPath, String fileType) throws IOException {
        Workbook workbook = null;
        String newFileType;

        if (null == tempPath || "".equals(tempPath)) {
            if (null != fileType && !"".equals(fileType)) {
                // 格式判断
                if (ExcelType.EXCELTYPE_03.equals(fileType)) {
                    workbook = new HSSFWorkbook();
                } else if (ExcelType.EXCELTYPE_07.equals(fileType)) {
                    workbook = new XSSFWorkbook();
                }
            }
        } else {
            newFileType = tempPath.substring(tempPath.lastIndexOf("."));

            File file = new File(tempPath);
            // 格式判断
            if (ExcelType.EXCELTYPE_03.equals(newFileType)) {
                workbook = new HSSFWorkbook(new FileInputStream(file));
            } else if (ExcelType.EXCELTYPE_07.equals(newFileType)) {
                workbook = new XSSFWorkbook(new FileInputStream(file));
            }
        }

        return workbook;
    }

    /**
     * 设置单个格子值
     *
     * @param workbook           workbook
     * @param cell               cell
     * @param moneyCellStyle     金额样式
     * @param numberCellStyle    数值样式
     * @param characterCellStyle 字符样式
     * @param warpTextStyle      换行样式
     * @param obj                model
     * @param field              字段
     */
    private static void setCellValue(Workbook workbook, Cell cell, CellStyle moneyCellStyle, CellStyle numberCellStyle, CellStyle characterCellStyle, CellStyle warpTextStyle, Object obj, Field field) throws IllegalAccessException {
        Type type = field.getType();
        ExcelCell excelCell = field.getAnnotation(ExcelCell.class);
        String excelFormat = excelCell.format();
        Object modelValue = field.get(obj);
        if (null == modelValue) {
//            cell.setCellType(Cell.CELL_TYPE_STRING);
//            CellStyle isStyle = doPrecision(workbook, cell, characterCellStyle);
//            cell.setCellStyle(isStyle);
//            return;
            modelValue = "";
        }
        String value = modelValue.toString();
        if (type == String.class) {
            cell.setCellValue(value);
            cell.setCellType(Cell.CELL_TYPE_STRING);
//            CellStyle isStyle = doPrecision(workbook, cell, warpTextStyle);
            setDefaultBorder(cell.getCellStyle());
        } else if (type == Integer.class || type == int.class) {
            cell.setCellValue(Integer.parseInt(value));
            cell.setCellType(Cell.CELL_TYPE_NUMERIC);
            CellStyle isStyle = doPrecision(workbook, cell, characterCellStyle);
            cell.setCellStyle(isStyle);
        } else if (type == Long.class || type == long.class) {
            cell.setCellValue(Long.parseLong(value));
            cell.setCellType(Cell.CELL_TYPE_NUMERIC);
            CellStyle isStyle = doPrecision(workbook, cell, numberCellStyle);
            cell.setCellStyle(isStyle);
        } else if (type == Boolean.class || type == boolean.class) {
            cell.setCellValue(value);
            cell.setCellType(Cell.CELL_TYPE_BOOLEAN);
            CellStyle isStyle = doPrecision(workbook, cell, characterCellStyle);
            cell.setCellStyle(isStyle);
        } else if (type == Date.class) {
            if ("".equals(excelFormat)) {
                cell.setCellValue((new SimpleDateFormat("yyyy-MM-dd")).format((Date) modelValue));
                // curCell.setCellType(Cell.CELL_TYPE_NUMERIC);
            } else {
                cell.setCellValue((new SimpleDateFormat(excelFormat)).format((Date) modelValue));
            }
            CellStyle isStyle = doPrecision(workbook, cell, characterCellStyle);
            cell.setCellStyle(isStyle);
        } else if (type == BigDecimal.class || type == Double.class || type == double.class) {
            cell.setCellType(Cell.CELL_TYPE_NUMERIC);
            cell.setCellValue(Double.parseDouble(value));
            if ("".equals(excelFormat)) {
                CellStyle isStyle = doPrecision(workbook, cell, moneyCellStyle);
                cell.setCellStyle(isStyle);
            } else {
                CellStyle formatCellStyle = workbook.createCellStyle();
                DataFormat formatDataFormat = workbook.createDataFormat();
                formatCellStyle.setDataFormat(formatDataFormat.getFormat(excelFormat));
                formatCellStyle.setBorderBottom(CellStyle.BORDER_THIN);
                formatCellStyle.setBorderLeft(CellStyle.BORDER_THIN);
                formatCellStyle.setBorderTop(CellStyle.BORDER_THIN);
                formatCellStyle.setBorderRight(CellStyle.BORDER_THIN);
                CellStyle isStyle = doPrecision(workbook, cell, formatCellStyle);
                cell.setCellStyle(isStyle);
            }
        }
    }

    /**
     * 添加合并单元格
     *
     * @param sheet    sheet 页
     * @param firstRow 首行
     * @param lastRow  尾行
     * @param firstCol 首 cell
     * @param lastCol  尾 cell
     */
    private static void addMergedRegion(Workbook workbook, Sheet sheet, CellStyle fromCellStyle, int firstRow, int lastRow, int firstCol, int lastCol) {
        CellRangeAddress region = new CellRangeAddress(firstRow, lastRow, firstCol, lastCol);
        sheet.addMergedRegion(region);

        // 边框和边框颜色
        RegionUtil.setBorderTop(fromCellStyle.getBorderTop(), region, sheet, workbook);// 上边框
        RegionUtil.setBorderBottom(fromCellStyle.getBorderBottom(), region, sheet, workbook);// 下边框
        RegionUtil.setBorderLeft(fromCellStyle.getBorderLeft(), region, sheet, workbook);// 左边框
        RegionUtil.setBorderRight(fromCellStyle.getBorderRight(), region, sheet, workbook);// 右边框

        RegionUtil.setTopBorderColor(fromCellStyle.getTopBorderColor(), region, sheet, workbook);
        RegionUtil.setBottomBorderColor(fromCellStyle.getBottomBorderColor(), region, sheet, workbook);
        RegionUtil.setLeftBorderColor(fromCellStyle.getLeftBorderColor(), region, sheet, workbook);
        RegionUtil.setRightBorderColor(fromCellStyle.getRightBorderColor(), region, sheet, workbook);
    }

    /**
     * 处理单元格样式
     *
     * @param workbook workbook
     * @param cell     cell
     * @param style    样式
     * @return style
     */
    private static CellStyle doPrecision(Workbook workbook, Cell cell, CellStyle style) {
//        CellStyle newCellStyle = workbook.createCellStyle();
        //兼容原来格式
        CellStyle iStyle = cell.getCellStyle();
        iStyle.cloneStyleFrom(style);

        return iStyle;
    }

    /**
     * 复制合并单元格
     *
     * @param fromWorkbook   fromWorkbook
     * @param toWorkbook     toWorkbook
     * @param fromSheetIndex fromSheetIndex
     * @param toSheetIndex   toSheetIndex
     * @param position       position
     * @throws Exception e
     */
    public static void copyMergedRegions(Workbook fromWorkbook, Workbook toWorkbook, int fromSheetIndex, int toSheetIndex, int position) throws Exception {
        Sheet fromSheet = fromWorkbook.getSheetAt(fromSheetIndex);
        int sheetMergerCount = fromSheet.getNumMergedRegions();
        for (int i = 0; i < sheetMergerCount; i++) {
            CellRangeAddress cra = fromSheet.getMergedRegion(i);
            int firstRow = cra.getFirstRow(); // 合并单元格cell起始行
            int firstCol = cra.getFirstColumn(); // 合并单元格cell起始列
            int lastRow = cra.getLastRow(); // 合并单元格cell结束行
            int lastCol = cra.getLastColumn(); // 合并单元格cell结束列
            setMergedRegionCell(toWorkbook, toSheetIndex, firstRow + position, lastRow + position, firstCol, lastCol);
        }
    }

    /**
     * 设置合并单元格
     *
     * @param workbook   workbook
     * @param startSheet startSheet
     * @param firstRow   firstRow
     * @param lastRow    lastRow
     * @param firstCol   firstCol
     * @param lastCol    lastCol
     * @return Workbook
     * @throws Exception e
     */
    private static Workbook setMergedRegionCell(Workbook workbook, int startSheet, int firstRow, int lastRow, int firstCol, int lastCol) throws Exception {
        Sheet sheet = workbook.getSheetAt(startSheet);
        sheet.addMergedRegion(new CellRangeAddress(firstRow, lastRow, firstCol, lastCol));
        //获取合并单元格
        Row row = sheet.getRow(firstRow);
        if (null == row) {
            row = sheet.createRow(firstRow);
        }
        Cell cell = row.getCell(firstCol);
        if (null == cell) {
            cell = row.createCell(firstCol);
        }
        CellStyle cellStyle = workbook.createCellStyle();
        //合并单元格默认居中
        cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
        cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        cell.setCellStyle(cellStyle);
        return workbook;
    }

    /**
     * 复制行
     *
     * @param fromWorkbook   源工作簿 .
     * @param toWorkbook     目标工作簿 .
     * @param fromSheetIndex 源Sheet .
     * @param toSheetIndex   目标Sheet .
     * @param startRow       开始行 .
     * @param endRow         结束行 .
     * @param position       位置(偏移量) .
     * @throws Exception e
     */
    public static void copyRows(Workbook fromWorkbook, Workbook toWorkbook, int fromSheetIndex, int toSheetIndex, int startRow, int endRow, int position) throws Exception {
        if ((startRow < 0) || (endRow < 0)) {
            return;
        }
        Sheet fromSheet = fromWorkbook.getSheetAt(fromSheetIndex);
        Sheet toSheet = toWorkbook.getSheetAt(toSheetIndex);
        // 设置列宽
        for (int i = startRow; i <= endRow; i++) {
            Row fromRow = fromSheet.getRow(i);
            if (fromRow != null) {
                for (int j = fromRow.getLastCellNum(); j >= fromRow.getFirstCellNum(); j--) {
                    toSheet.setColumnWidth(j, fromSheet.getColumnWidth(j));
                    toSheet.setColumnHidden(j, false);
                }
                //列宽设置一次即可
                break;
            }
        }
        //复制行(开始至结束行)
        for (int i = startRow; i <= endRow; i++) {
            Row fromRow = fromSheet.getRow(i);
            if (null == fromRow) {
                continue;
            }
            //新建行
            Row toRow = toSheet.getRow(i - startRow + position);
            //为了保留原有样式
            if (null == toRow) {
                toRow = toSheet.createRow(i - startRow + position);
            }
            copyCells(fromWorkbook, toWorkbook, fromRow, toRow);
        }
    }

    /**
     * 复制列 .
     *
     * @param fromWorkbook 源工作簿 .
     * @param toWorkbook   目标工作簿 .
     * @param fromRow      源行记录 .
     * @param toRow        目标行记录 .
     * @throws Exception .
     */
    private static void copyCells(Workbook fromWorkbook, Workbook toWorkbook, Row fromRow, Row toRow) throws Exception {
        toRow.setHeight(fromRow.getHeight());
        //复制列
        for (int j = fromRow.getFirstCellNum(); j <= fromRow.getPhysicalNumberOfCells(); j++) {
            Cell fromCell = fromRow.getCell(j);
            if (null == fromCell) {
                continue;
            }
            Cell toCell = toRow.getCell(j);
            //为了保留原有样式
            if (null == toCell) {
                toCell = toRow.createCell(j);
            }
            copyCell(fromWorkbook, toWorkbook, fromCell, toCell);
        }
    }

    /**
     * 复制单元格 .
     *
     * @param fromWorkbook 源工作簿 .
     * @param toWorkbook   目标工作簿 .
     * @param fromCell     源单元格 .
     * @param toCell       源单元格 .
     * @throws Exception .
     */
    private static void copyCell(Workbook fromWorkbook, Workbook toWorkbook, Cell fromCell, Cell toCell) throws Exception {
        CellStyle fromStyle = fromCell.getCellStyle();
        //不能直接获取CellStyle,否则会导致其他空白单元格也会设置样式
        //CellStyle toStyle = toCell.getCellStyle();
        CellStyle toStyle = toWorkbook.createCellStyle();
        copyCellStyle(fromStyle, toStyle);

        //字体
        Font fromFont = fromWorkbook.getFontAt(fromStyle.getFontIndex());
        Font toFont = toWorkbook.getFontAt(toStyle.getFontIndex());
        copyCellFont(fromFont, toFont);

        toStyle.setFont(toFont);
        toCell.setCellStyle(toStyle);

        int cellType = fromCell.getCellType();
        switch (cellType) {
            case Cell.CELL_TYPE_BLANK:
                toCell.setCellValue("");
                break;
            case Cell.CELL_TYPE_BOOLEAN:
                toCell.setCellValue(fromCell.getBooleanCellValue());
                break;
            case Cell.CELL_TYPE_STRING:
                toCell.setCellValue(fromCell.getRichStringCellValue());
                break;
            case Cell.CELL_TYPE_NUMERIC:
                toCell.setCellValue(fromCell.getNumericCellValue());
                break;
            case Cell.CELL_TYPE_FORMULA:
                toCell.setCellFormula(parseFormula(fromCell.getCellFormula()));
                break;
            case Cell.CELL_TYPE_ERROR:
                toCell.setCellErrorValue(fromCell.getErrorCellValue());
                break;
            default:
                toCell.setCellValue("");
                break;
        }
    }

    /**
     * 复制单元格样式
     *
     * @param fromStyle 源样式
     * @param toStyle   目标样式
     */
    private static void copyCellStyle(CellStyle fromStyle, CellStyle toStyle) {
        toStyle.cloneStyleFrom(fromStyle);

        if (fromStyle.getAlignment() != 0)
            toStyle.setAlignment(fromStyle.getAlignment());
        // 边框和边框颜色
        if (fromStyle.getBorderBottom() != 1)
            toStyle.setBorderBottom(fromStyle.getBorderBottom());
        if (fromStyle.getBorderLeft() != 1)
            toStyle.setBorderLeft(fromStyle.getBorderLeft());
        if (fromStyle.getBorderRight() != 1)
            toStyle.setBorderRight(fromStyle.getBorderRight());
        if (fromStyle.getBorderTop() != 1)
            toStyle.setBorderTop(fromStyle.getBorderTop());
        if (fromStyle.getTopBorderColor() != 0)
            toStyle.setTopBorderColor(fromStyle.getTopBorderColor());
        if (fromStyle.getBottomBorderColor() != 0)
            toStyle.setBottomBorderColor(fromStyle.getBottomBorderColor());
        if (fromStyle.getRightBorderColor() != 0)
            toStyle.setRightBorderColor(fromStyle.getRightBorderColor());
        if (fromStyle.getLeftBorderColor() != 0)
            toStyle.setLeftBorderColor(fromStyle.getLeftBorderColor());

        // 背景和前景
        if (fromStyle.getFillBackgroundColor() != 64)
            toStyle.setFillBackgroundColor(fromStyle.getFillBackgroundColor());
        if (fromStyle.getFillForegroundColor() != 64)
            toStyle.setFillForegroundColor(fromStyle.getFillForegroundColor());

        if (fromStyle.getDataFormat() != 49)
            toStyle.setDataFormat(fromStyle.getDataFormat());
        if (fromStyle.getFillPattern() != 0)
            toStyle.setFillPattern(fromStyle.getFillPattern());
        if (fromStyle.getHidden())
            toStyle.setHidden(fromStyle.getHidden());
        if (fromStyle.getIndention() != 0)
            toStyle.setIndention(fromStyle.getIndention());
        if (!fromStyle.getLocked())
            toStyle.setLocked(fromStyle.getLocked());
        if (fromStyle.getRotation() != 0)
            toStyle.setRotation(fromStyle.getRotation());
        if (fromStyle.getVerticalAlignment() != 1)
            toStyle.setVerticalAlignment(fromStyle.getVerticalAlignment());

        // 自动换行
        if (fromStyle.getWrapText())
            toStyle.setWrapText(fromStyle.getWrapText());
    }

    /**
     * 复制字体样式
     *
     * @param fromFont 源字体 .
     * @param toFont   目标字体 .
     */
    private static void copyCellFont(Font fromFont, Font toFont) {
        toFont.setBoldweight(fromFont.getBoldweight());
        toFont.setCharSet(fromFont.getCharSet());
        toFont.setColor(fromFont.getColor());
        toFont.setFontHeight(fromFont.getFontHeight());
        toFont.setFontHeightInPoints(fromFont.getFontHeightInPoints());
        toFont.setFontName(fromFont.getFontName());
        toFont.setItalic(fromFont.getItalic());
        toFont.setStrikeout(fromFont.getStrikeout());
        toFont.setTypeOffset(fromFont.getTypeOffset());
        toFont.setUnderline(fromFont.getUnderline());
    }

    /**
     * 解析单元格公式
     *
     * @param pPOIFormula
     * @return .
     */
    private static String parseFormula(String pPOIFormula) {
        final String cstReplaceString = "ATTR(semiVolatile)"; //$NON-NLS-1$
        StringBuffer result;
        int index;
        result = new StringBuffer();
        index = pPOIFormula.indexOf(cstReplaceString);
        if (index >= 0) {
            result.append(pPOIFormula.substring(0, index));
            result.append(pPOIFormula.substring(index + cstReplaceString.length()));
        } else {
            result.append(pPOIFormula);
        }
        return result.toString();
    }

    /**
     * @param str             单元格需要放入的 字符串
     * @param fontCountInline 该单元格每行多少个汉字 全角为1 英文或符号为0.5
     * @return 计算后的数据
     */
    private static float getExcelCellAutoHeight(String str, float fontCountInline) {
        float defaultRowHeight = 12.00f;//每一行的高度指定
        float defaultCount = 0.00f;
        for (int i = 0; i < str.length(); i++) {
            float ff = getRegex(str.substring(i, i + 1));
            defaultCount = defaultCount + ff;
        }
        return ((int) (defaultCount / fontCountInline) + 1) * defaultRowHeight;//计算
    }

    /**
     * 根据字符类型判断
     *
     * @param charStr 字符串
     * @return 计算后的数据
     */
    private static float getRegex(String charStr) {

        if (Objects.equals(charStr, " ")) {
            return 0.5f;
        }
        // 判断是否为字母或字符
        if (Pattern.compile("^[A-Za-z0-9]+$").matcher(charStr).matches()) {
            return 0.5f;
        }
        // 判断是否为全角

        if (Pattern.compile("[\u4e00-\u9fa5]+$").matcher(charStr).matches()) {
            return 1.00f;
        }
        //全角符号 及中文
        if (Pattern.compile("[^x00-xff]").matcher(charStr).matches()) {
            return 1.00f;
        }
        return 0.5f;
    }

    /**
     * 获取标题样式
     *
     * @param workbook workbook
     * @return moneyCellStyle
     */
    private static CellStyle getTitleCellStyle(Workbook workbook) {
        // 单元格金额格式
        CellStyle titleCellStyle = workbook.createCellStyle();

        /*
         * 边框
         */
        titleCellStyle.setBorderBottom(CellStyle.BORDER_THIN);
        titleCellStyle.setBorderLeft(CellStyle.BORDER_THIN);
        titleCellStyle.setBorderTop(CellStyle.BORDER_THIN);
        titleCellStyle.setBorderRight(CellStyle.BORDER_THIN);
        // 创建一个居中格式
        titleCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        // 垂直居中
        titleCellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        titleCellStyle.setFont(getFont(workbook));

        return titleCellStyle;
    }

    /**
     * 获取金额类型样式
     *
     * @param workbook workbook
     * @return moneyCellStyle
     */
    private static CellStyle getMoneyCellStyle(Workbook workbook) {
        // 单元格金额格式
        CellStyle moneyCellStyle = workbook.createCellStyle();

        DataFormat moneyDataFormat = workbook.createDataFormat();

        // 默认样式
        moneyCellStyle.setDataFormat(moneyDataFormat.getFormat("#,##0.00"));

        /*
         * 边框
         */
        moneyCellStyle.setBorderBottom(CellStyle.BORDER_THIN);
        moneyCellStyle.setBorderLeft(CellStyle.BORDER_THIN);
        moneyCellStyle.setBorderTop(CellStyle.BORDER_THIN);
        moneyCellStyle.setBorderRight(CellStyle.BORDER_THIN);
        moneyCellStyle.setFont(getFont(workbook));

        return moneyCellStyle;
    }

    /**
     * 获取数字类型样式
     *
     * @param workbook workbook
     * @return numberCellStyle
     */
    private static CellStyle getNumberCellStyle(Workbook workbook) {
        // 单元格数量格式
        CellStyle numberCellStyle = workbook.createCellStyle();

        DataFormat numberDataFormat = workbook.createDataFormat();

        // 默认样式
        numberCellStyle.setDataFormat(numberDataFormat.getFormat("#,##"));
        /*
         * 边框
         */
        numberCellStyle.setBorderBottom(CellStyle.BORDER_THIN);
        numberCellStyle.setBorderLeft(CellStyle.BORDER_THIN);
        numberCellStyle.setBorderTop(CellStyle.BORDER_THIN);
        numberCellStyle.setBorderRight(CellStyle.BORDER_THIN);
        numberCellStyle.setFont(getFont(workbook));

        return numberCellStyle;
    }

    /**
     * 获取字符类型样式
     *
     * @param workbook workbook
     * @return commCellStyle
     */
    private static CellStyle getCharacterCellStyle(Workbook workbook) {
        CellStyle characterCellStyle = workbook.createCellStyle();

        characterCellStyle.setBorderBottom(CellStyle.BORDER_THIN);
        characterCellStyle.setBorderLeft(CellStyle.BORDER_THIN);
        characterCellStyle.setBorderTop(CellStyle.BORDER_THIN);
        characterCellStyle.setBorderRight(CellStyle.BORDER_THIN);
        characterCellStyle.setFont(getFont(workbook));

        return characterCellStyle;
    }

    /**
     * 获取序号类型样式
     *
     * @param workbook workbook
     * @return noCellStyle
     */
    private static CellStyle getNoCellStyle(Workbook workbook) {
        //序号样式
        CellStyle noCellStyle = workbook.createCellStyle();

        noCellStyle.setBorderBottom(CellStyle.BORDER_THIN);
        noCellStyle.setBorderLeft(CellStyle.BORDER_THIN);
        noCellStyle.setBorderTop(CellStyle.BORDER_THIN);
        noCellStyle.setBorderRight(CellStyle.BORDER_THIN);
        noCellStyle.setAlignment(CellStyle.ALIGN_CENTER);
        noCellStyle.setFont(getFont(workbook));

        return noCellStyle;
    }

    /**
     * 获取换行样式
     *
     * @param workbook workbook
     * @return wrapTextStyle
     */
    private static CellStyle getWrapTextStyle(Workbook workbook) {
        // 换行样式
        CellStyle wrapTextStyle = workbook.createCellStyle();

        wrapTextStyle.setWrapText(true);
        wrapTextStyle.setBorderBottom(CellStyle.BORDER_THIN);
        wrapTextStyle.setBorderLeft(CellStyle.BORDER_THIN);
        wrapTextStyle.setBorderTop(CellStyle.BORDER_THIN);
        wrapTextStyle.setBorderRight(CellStyle.BORDER_THIN);
        // wrapTextStyle.setFont(getFont(workbook));

        return wrapTextStyle;
    }

    /**
     * 默认边框
     *
     * @param style cell style
     */
    private static void setDefaultBorder(CellStyle style) {
        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBorderLeft(CellStyle.BORDER_THIN);
        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setBorderRight(CellStyle.BORDER_THIN);
    }

    /**
     * 默认字体
     *
     * @param workbook workbook
     * @return font
     */
    private static Font getFont(Workbook workbook) {
        Font font = workbook.createFont();

        font.setFontName("宋体");
        font.setFontHeightInPoints((short) 12);

        return font;
    }

    /**
     * 简易下载Workbook文件
     *
     * @param request  request
     * @param response response
     * @param workbook workbook
     * @param fileName fileName
     * @throws Exception e
     */
    public static void download(HttpServletRequest request, HttpServletResponse response, Workbook workbook, String fileName) throws Exception {
        //解决 IE下载文件名乱码
        String Agent = request.getHeader("User-Agent");
        if (null != Agent) {
            Agent = Agent.toLowerCase();
            if (Agent.contains("firefox")) {
                fileName = new String(fileName.getBytes(), "iso8859-1");
            } else if (Agent.contains("msie")) {
                fileName = java.net.URLEncoder.encode(fileName, "UTF-8");
            } else {
                fileName = java.net.URLEncoder.encode(fileName, "UTF-8");
            }
        }
		/*response.setHeader("Content-Disposition", "attachment;filename="
				+ new String(fileName.getBytes("utf-8"), "iso8859-1"));*/
        response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
        response.setContentType("application/ynd.ms-excel;charset=UTF-8");
        OutputStream out = response.getOutputStream();
        workbook.write(out);
        out.flush();
        out.close();
    }

    /**
     * 保存Workbook文件
     *
     * @param path       路径
     * @param fileName   文件名
     * @param fileFormat 文件类型
     * @param workbook   workbook
     * @throws Exception e
     */
    public static void saveWorkbook(String path, String fileName, String fileFormat, Workbook workbook) throws Exception {
        File file = new File(path, fileName + fileFormat);
        if (!file.exists()) {
            if (!file.getParentFile().exists()) {
                file.getParentFile().mkdirs();
            }
            file.createNewFile();
        }
        OutputStream out = new FileOutputStream(file);
        workbook.write(out);
        out.flush();
        out.close();
    }

}
